﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Web;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Runtime.CompilerServices;


using System.Linq;
using System.Data.Common;
using System.Reflection.Emit;
using System.Linq.Expressions;

namespace lxwmvc
{
    /// <summary>
    /// 执行数据库相关操作
    /// </summary>
    public static class sqlhelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        internal static string connectstring = System.Configuration.ConfigurationManager.AppSettings["connectstring"].ToString();

        #region 外部方法，可以不用处理
        /// <summary>
        /// 单独给外部的方法，不存在事务
        /// </summary>
        /// <param name="commandtext">sql语句</param>
        /// <param name="connectstring">连接字符串</param>
        /// <returns></returns>
        public static DataSet out_getds(string commandtext, string connectstring = "")
        {
            if (connectstring == null || connectstring == "") connectstring = sqlhelper.connectstring;
            using (SqlConnection conn = new SqlConnection(connectstring))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = commandtext;
                    cmd.CommandType = CommandType.Text;

                    dataloger.debug("sql:" + commandtext);

                    try
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();

                            // Fill the DataSet using default values for DataTable names, etc
                            da.Fill(ds);

                            // Detach the OracleParameters from the command object, so they can be used again
                            cmd.Parameters.Clear();

                            return ds;
                        }
                    }
                    catch (Exception ex)
                    {
                        dataloger.error("sql:" + commandtext, ex);
                        throw new Exception(ex.Message + "sql:" + commandtext);
                    }
                }
            }
        }

        /// <summary>
        /// 外部执行代码
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="connectstring"></param>
        /// <returns></returns>
        public static int out_exec(string commandText, string connectstring = "")
        {
            if (connectstring == null || connectstring == "") connectstring = sqlhelper.connectstring;
            using (SqlConnection conn = new SqlConnection(connectstring))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = commandText;
                    cmd.CommandType = CommandType.Text;

                    dataloger.debug("sql:" + commandText);
                    try
                    {
                        return cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        dataloger.error("sql:" + commandText, ex);
                        throw new Exception(ex.Message + "sql:" + commandText);
                    }
                }
            }
        }

        #endregion
        /// <summary>
        /// 得到信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandText"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        internal static T get<T>(string commandText, params object[] args)
        {
            try
            {
                SqlConnection conn = dbconnect();
                dataloger.debug("sql:" + commandText);
                var tran = begintransaction(conn);

                //AnonymousType
                if (args != null && args.Length == 1 && (IsAnonymousType(args[0].GetType()) || IsGenericType(args[0].GetType())))
                {
                    return conn.ExecuteScalar<T>(commandText, args[0], tran);
                }
                else
                {
                    string sql;
                    Dictionary<string, object> di = ProcessParams(commandText, args, out sql);
                    return conn.ExecuteScalar<T>(sql, di, tran);
                }
            }
            catch (Exception ex)
            {
                dataloger.error("sql:" + commandText, ex);
                throw new Exception(ex.Message + "sql:" + commandText);
            }
        }

        /// <summary>
        /// 判断匿名类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        static bool IsAnonymousType(Type type)
        {
            //if (type. typeof(IDictionary)) return false;

            if (!type.IsGenericType) return false;
            if ((type.Attributes & TypeAttributes.NotPublic) != TypeAttributes.NotPublic)
                return false;
            if (!Attribute.IsDefined(type, typeof(CompilerGeneratedAttribute), false))
                return false;

            //+		args[0].GetType()	{Name = "Dictionary`2" FullName = "System.Collections.Generic.Dictionary`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"}	System.Type {System.RuntimeType}

            return type.Name.Contains("AnonymousType");
        }

        /// <summary>
        /// 判断是否是Dictionary类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        static bool IsGenericType(Type type)
        {
            return type.Name.Contains("Dictionary");
        }

        static Regex rxParams = new Regex(@"(?<!@)@\w+", RegexOptions.Compiled);
        static Dictionary<string, object> ProcessParams(string _sql, object[] args_src,out string outsql)
        {
            Dictionary<string, object> args_dest = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);

            var sql = _sql;

            if (args_src != null && args_src.Length > 0)
            {
                sql = rxParams.Replace(sql, m =>
                {
                    string param = m.Value.Substring(1);
                    object val = "";
                    int paramIndex;
                    if (int.TryParse(param, out paramIndex))
                    {
                        // Numbered parameter
                        if (paramIndex < 0 || paramIndex >= args_src.Length)
                            throw new ArgumentOutOfRangeException(string.Format("Parameter '@{0}' specified but only {1} parameters supplied (in `{2}`)", paramIndex, args_src.Length, sql));
                        //args_dest.Add(args_src[paramIndex]);
                        val = args_src[paramIndex];
                    }
                    else
                    {
                        // Look for a property on one of the arguments with this name
                        bool found = false;
                        foreach (var o in args_src)
                        {
                            var pi = o.GetType().GetProperty(param);
                            if (pi != null)
                            {
                                //args_dest.Add(pi.GetValue(o, null));
                                val = pi.GetValue(o, null);
                                found = true;
                                break;
                            }
                        }

                        if (!found)
                            throw new ArgumentException(string.Format("Parameter '@{0}' specified but none of the passed arguments have a property with this name (in '{1}')", param, sql));
                    }

                    var key = "@lxw" + args_dest.Count;
                    args_dest[key] = val;
                    return key;
                }
                );
            }

            outsql = sql;

            return args_dest;
        }


        ///// <summary>
        ///// 返回一个model信息
        ///// </summary>
        ///// <param name="sql">输出has队列的sql语句，只输出第一行</param>
        ///// <param name="args"></param>
        ///// <returns></returns>
        //internal static supermodel model(string sql,params object[] args)
        //{
        //    supermodel sm = supermodel.create();
        //    var dt = sqlhelper.query(sql,args).totable();


        //    if (dt != null && dt.Rows.Count > 0)
        //    {
        //        for (int i = 0; i < dt.Columns.Count; i++)
        //        {
        //            sm[dt.Columns[i].ColumnName.ToLower()] = dt.Rows[0][i];
        //        }
        //    }

        //    return sm;
        //}

        #region trans

        /// <summary>
        /// 一次请求 就一完整的sql语句
        /// </summary>
        static string context_connect = "context_connect";
        static SqlConnection dbconnect()
        {
            if (HttpContext.Current.Items[context_connect] != null)
                return (SqlConnection)HttpContext.Current.Items[context_connect];
            else
            {
                SqlConnection conn = new SqlConnection(connectstring);

                if (conn.State != ConnectionState.Open)
                    conn.Open();
                HttpContext.Current.Items[context_connect] = conn;

                return conn;
            }
        }
        /// <summary>
        /// 当前用户的session
        /// </summary>
         static string lxw_transaction = "lxw_transaction";
         static string has_transaction = "has_transaction";

         static SqlTransaction begintransaction(SqlConnection conn)
         {
             if (HttpContext.Current.Items[has_transaction] == null) return null;

             transAttribute tran = (transAttribute)HttpContext.Current.Items[has_transaction];
             var transaction = HttpContext.Current.Items[lxw_transaction];
             SqlTransaction sqltransaction = null;
             if (transaction != null)
             {
                 sqltransaction = (SqlTransaction)transaction;
                 //command.Transaction = sqltransaction;
             }
             else
             {
                 sqltransaction = conn.BeginTransaction(tran.iso);
                 //command.Transaction = sqltransaction;
                 HttpContext.Current.Items[lxw_transaction] = sqltransaction;
             }

             return sqltransaction;
         }
        /// <summary>
        /// 关闭数据源
        /// </summary>
        internal static void dbconnectclose()
        {
            if (HttpContext.Current.Items[context_connect] != null)
            {
                SqlConnection conn = (SqlConnection)HttpContext.Current.Items[context_connect];
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        internal static void starttranscation(Attribute trans)
        {
            HttpContext.Current.Items[has_transaction] = trans;
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        internal static void transactioncommit()
        {
            if (HttpContext.Current.Items[has_transaction]==null)
            {
                dbconnectclose();
                return;
            }

            var transaction = HttpContext.Current.Items[lxw_transaction];
            if (transaction != null)
            {
                SqlTransaction sqltransaction = (SqlTransaction)transaction;
                sqltransaction.Commit();

                HttpContext.Current.Items.Remove(lxw_transaction);
            }
            dbconnectclose();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        internal static void transactionrollback()
        {
            if (HttpContext.Current.Items[has_transaction] != null)
            {
                dbconnectclose();
                return;
            }
            var transaction = HttpContext.Current.Items[lxw_transaction];
            if (transaction != null)
            {
                SqlTransaction sqltransaction = (SqlTransaction)transaction;
                sqltransaction.Rollback();

                HttpContext.Current.Items.Remove(lxw_transaction);
            }
            dbconnectclose();
        }

        #endregion

        /// <summary>
        /// 执行sql语句并返回dataset
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        internal static IEnumerable<dynamic> query(string commandText,params object[] args)
        {
            try
            {
                SqlConnection conn = dbconnect();
                //dataloger.debug("sql:" + commandText);

                var trans = begintransaction(conn);

                if (args != null && args.Length == 1 && (IsAnonymousType(args[0].GetType()) || IsGenericType(args[0].GetType())))
                {
                    return conn.Query(commandText, args[0], trans);
                }
                else
                {
                    string sql;
                    Dictionary<string, object> di = ProcessParams(commandText, args, out sql);
                    return conn.Query(sql, di, trans);
                }
            }
            catch (Exception ex)
            {
                dataloger.error("sql:" + commandText, ex);
                throw new Exception(ex.Message + "sql:" + commandText);
            }
        }

        /// <summary>
        /// 返回特定类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandText"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        internal static IEnumerable<T> query<T>(string commandText, params object[] args)
        {
            try
            {
                SqlConnection conn = dbconnect();
                //dataloger.debug("sql:" + commandText);

                var trans = begintransaction(conn);

                if (args != null && args.Length == 1 && (IsAnonymousType(args[0].GetType()) || IsGenericType(args[0].GetType())))
                {
                    return conn.Query<T>(commandText, args[0], trans);
                }
                else
                {
                    string sql;
                    Dictionary<string, object> di = ProcessParams(commandText, args, out sql);
                    return conn.Query<T>(sql, di, trans);
                }
            }
            catch (Exception ex)
            {
                dataloger.error("sql:" + commandText, ex);
                throw new Exception(ex.Message + "sql:" + commandText);
            }
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        internal static int execute(string commandText, params object[] args)
        {
            try
            {
                SqlConnection conn = dbconnect();
                //dataloger.debug("sql:" + commandText);

                var trans = begintransaction(conn);

                if (args != null && args.Length == 1 && (IsAnonymousType(args[0].GetType()) || IsGenericType(args[0].GetType())))
                {
                    return conn.Execute(commandText, args[0], trans);
                }
                else
                {
                    string sql;
                    Dictionary<string, object> di = ProcessParams(commandText, args, out sql);
                    return conn.Execute(sql, di, trans);
                }                
            }          
            catch (Exception ex)
            {
                dataloger.error("sql:" + commandText, ex);
                throw new Exception(ex.Message + "sql:" + commandText);
            }
        }

        /// <summary>
        /// 执行方法CONN里面的所有方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fun"></param>
        /// <returns></returns>
        internal static T FUN<T>(Func<IDbConnection,IDbTransaction,T> fun)
        {
            try
            {
                SqlConnection conn = dbconnect();
                var trans = begintransaction(conn);
                return (T)fun(conn, trans);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        internal static void FUN(Action<IDbConnection, IDbTransaction> fun)
        {
            try
            {
                SqlConnection conn = dbconnect();
                var trans = begintransaction(conn);
                fun(conn, trans);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }       
        /// <summary>
        /// 实现MSSQL分页 
        /// </summary>
        /// <param name="strtablename"></param>
        /// <param name="strfileds"></param>
        /// <param name="strorderfileds"></param>
        /// <param name="ipageindex"></param>
        /// <param name="ipagesize"></param>
        /// <param name="strwhere"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        internal static pagerinfo showpager(string strtablename, string strfileds, string strorderfileds, int ipageindex, int ipagesize, string strwhere = "", params object[] args)
        {           
            if(string.IsNullOrEmpty(strorderfileds)) strorderfileds = "";
            else strorderfileds = " order by "+strorderfileds;

            //
            return pager("select " + strfileds + " from " + strtablename + " " + strorderfileds + (strwhere == "" ? "" : " where " + strwhere), ipageindex, ipagesize, args);
        }

        static Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        static Regex rxOrderBy = new Regex(@"\bORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        static Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        static bool SplitSqlForPaging(string sql, out string sqlCount, out string sqlSelectRemoved, out string sqlOrderBy)
        {
            sqlSelectRemoved = null;
            sqlCount = null;
            sqlOrderBy = null;

            // Extract the columns from "SELECT <whatever> FROM"
            var m = rxColumns.Match(sql);
            if (!m.Success)
                return false;

            // Save column list and replace with COUNT(*)
            Group g = m.Groups[1];
            sqlSelectRemoved = sql.Substring(g.Index);

            if (rxDistinct.IsMatch(sqlSelectRemoved))
                sqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
            else
                sqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);


            // Look for an "ORDER BY <whatever>" clause
            m = rxOrderBy.Match(sqlCount);
            if (!m.Success)
            {
                sqlOrderBy = null;
            }
            else
            {
                g = m.Groups[0];
                sqlOrderBy = g.ToString();
                sqlCount = sqlCount.Substring(0, g.Index) + sqlCount.Substring(g.Index + g.Length);
            }

            return true;
        }

        static void BuildPageQueries(long skip, long take, string sql, ref object[] args, out string sqlCount, out string sqlPage)
        {
            // Add auto select clause
            //if (EnableAutoSelect)
            //   sql = AddSelectClause<T>(sql);

            // Split the SQL into the bits we need
            string sqlSelectRemoved, sqlOrderBy;
            if (!SplitSqlForPaging(sql, out sqlCount, out sqlSelectRemoved, out sqlOrderBy))
                throw new Exception("Unable to parse SQL statement for paged query");

            sqlSelectRemoved = rxOrderBy.Replace(sqlSelectRemoved, "");
            if (rxDistinct.IsMatch(sqlSelectRemoved))
            {
                sqlSelectRemoved = "peta_inner.* FROM (SELECT " + sqlSelectRemoved + ") peta_inner";
            }

            sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",sqlOrderBy == null ? "ORDER BY (SELECT NULL)" : sqlOrderBy, sqlSelectRemoved, args.Length, args.Length + 1);
            args = args.Concat(new object[] { skip, skip + take }).ToArray();

        }
        // Fetch a page	
        //public Page<T> Page<T>(long page, long itemsPerPage, string sql, params object[] args) where T : new()
        internal static pagerinfo pager(string sql, object page, object itemsPerPage, params object[] args)
        {
            // Add auto select clause
            //if (EnableAutoSelect)
            //    sql = AddSelectClause<T>(sql);

            // Split the SQL into the bits we need
            string sqlCount, sqlPage;
            BuildPageQueries((page.toint32() - 1) * itemsPerPage.toint32(), itemsPerPage.toint32(), sql, ref args, out sqlCount, out sqlPage);

            // Setup the paged result
            var result = new pagerinfo();

            result.pagesize = itemsPerPage.toint32();
            result.recordcount = get<int>(sqlCount, args);
            var start = args[args.Length - 2].toint32();
            var end = args[args.Length - 1].toint32();

            /*
            if (result.recordcount <= start)
            {
                args[args.Length - 2] = (result.pagecount - 1) * result.pagesize;
                args[args.Length - 1] = result.pagecount * result.pagesize;

                //设置当前页
                page = result.pagecount;
            }
            */

            // Get the records
            result.data = query<supermodel>(sqlPage, args);
            result.setfirstindex(page.toint32(), itemsPerPage.toint32());

            // Done
            return result;
        }

        /// <summary>
        /// 唯一性判断
        /// </summary>
        /// <param name="table">表</param>
        /// <param name="column">字段名称</param>
        /// <param name="value">字段的内容</param>
        /// <param name="msg">错误提示</param>
        /// <param name="keycolumn">主键名称</param>
        /// <param name="keyvalue">主键的值</param>
        /// <param name="where">判断条件</param>
        internal static void weiyixing(string table, string column, string value, string msg, string keycolumn = "", string keyvalue = "", string where = "")
        {
            string sql = "select count(1) from {0} where {1}='{2}' {3} {4}";

            if (where != "") where = " and " + where;

            if (string.IsNullOrEmpty(keycolumn) || string.IsNullOrEmpty(keyvalue))
                sql = string.Format(sql, table, column, value, "", where);
            else
                sql = string.Format(sql, table, column, value, " and " + keycolumn + "<>" + keyvalue, where);

            int count = get<int>(sql);
            if (count > 0) throw new Exception(msg);

        }
    }


    /// <summary>
    /// 返回结果
    /// </summary>
    public class result
    {
        /// <summary>
        /// 是新增还是修改
        /// </summary>
        public action action { get; set; }
        /// <summary>
        /// 返回主键
        /// </summary>
        public int identity { get; set; }
        /// <summary>
        /// 传递的键值对应
        /// </summary>
        public supermodel model { get; set; }
    }

    /// <summary>
    /// 操作行为
    /// </summary>
    public enum action
    {
        /// <summary>
        /// 插入
        /// </summary>
        insert,
        /// <summary>
        /// 更新
        /// </summary>
        update
    }
}